package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;

import bean.Flight;
import bean.FlightUpdate;
import bean.Journey;
import exception.DAOException;

public class FlightDAOImpl extends DAO implements FlightDAO {

	public Flight changeFlightInfo(String flightNumber, String airlineName,
			String source, String destination, int numSeats, String pilotName,
			String intermediate, String intermediateArrivalTime,
			String intermediateDepartureTime, String timeOfFlight,
			double ticketPrice, String status, long flightId)
			throws DAOException {
		Statement stmt = null;
		Connection connection = null;
		Flight flight = new Flight();
		try {
			connection = getConnection();
			if (connection != null) {

				if (intermediateArrivalTime.equalsIgnoreCase("")) {
					intermediateArrivalTime = "00:00:00";
				}
				if (intermediateDepartureTime.equalsIgnoreCase("")) {
					intermediateDepartureTime = "00:00:00";
				}
				String SQL = "update ars1.flight set flight_number='"
						+ flightNumber + "'," + "airline='" + airlineName
						+ "',source='" + source + "',destination='"
						+ destination + "'," + "numSeats='" + numSeats
						+ "',pilot_name='" + pilotName
						+ "',intermediate_arrival_time='"
						+ intermediateArrivalTime + "',"
						+ "intermediate_departure_time='"
						+ intermediateDepartureTime + "',time_flight='"
						+ timeOfFlight + "'," + "ticket_price=" + ticketPrice
						+ ",flight_status='" + status + "' where  flight_id="
						+ flightId + "";
				System.out.println(SQL);
				stmt = connection.createStatement();
				stmt.executeUpdate(SQL);

				/*
				 * flight.setFlightNumber(rs.getString("flight_number"));
				 * flight.setNumSeats(rs.getInt("numSeats"));
				 * flight.setStatus(rs.getString("flight_status"));
				 * flight.setPrice(rs.getDouble("ticket_price"));
				 * flight.setAirlineName(rs.getString("airline"));
				 * flight.setSource(rs.getString("source"));
				 * flight.setDestination(rs.getString("destination"));
				 * flight.setIntermediate(rs.getString("intermediate"));
				 * flight.setFlightNumber(rs.getString("flight_number"));
				 * flight.setTimeOfFlight(rs.getString("time_flight"));
				 * flight.setIntermediateArrivalTime
				 * (rs.getString("intermediate_arrival_time"));
				 * flight.setIntermediateDepartureTime
				 * (rs.getString("intermediate_departure_time"));
				 */

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {

			}

		}
		return flight;
	}

	@Override
	public List<Flight> viewFlights() throws DAOException {
		Statement stmt = null;
		Connection connection = null;
		List<Flight> lstFlights = new ArrayList<Flight>();
		try {
			connection = getConnection();
			if (connection != null) {
				String SQL = "select * from ars1.flight;";
				stmt = connection.createStatement();
				stmt.setFetchSize(50);
				ResultSet resultSet = stmt.executeQuery(SQL);
				while (resultSet.next()) {

					Flight flight = new Flight();
					flight.setFlightId(resultSet.getLong("flight_id"));
					flight
							.setFlightNumber(resultSet
									.getString("flight_number"));
					flight.setPrice(resultSet.getDouble("ticket_price"));

					flight.setIntermediateArrivalTime(resultSet
							.getString("intermediate_arrival_time"));
					flight.setIntermediateDepartureTime(resultSet
							.getString("intermediate_departure_time"));
					flight.setNumSeats(resultSet.getInt("numSeats"));
					flight.setAirlineName(resultSet.getString("airline"));
					flight.setSource(resultSet.getString("source"));
					flight.setDestination(resultSet.getString("destination"));
					flight.setIntermediate(resultSet.getString("intermediate"));
					flight.setPilotName(resultSet.getString("pilot_name"));
					Time flightTime = resultSet.getTime("time_flight");
					flight.setTimeOfFlight(flightTime.toString());
					lstFlights.add(flight);
				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {

			}

		}
		return lstFlights;
	}

	// This is just s test main to test the methods in this class.
	// May be removed in future
	public static void main(String[] args) throws DAOException {
		FlightDAO f = new FlightDAOImpl();
		List<Flight> f1 = f.searchFlights("san jose", "las vegas", "2010-4-25",
				null, "one_way", 5);
		// System.out.println(f1.get(0).getTimeOfFlight());

	}

	@Override
	public List<Flight> searchFlights(String source, String destination,
			String startDate, String returnDate, String tripType,
			int numTravelers) throws DAOException {
		PreparedStatement stmt = null;
		Connection connection = null;
		List<Flight> lstFlights = new ArrayList<Flight>();
		try {
			connection = getConnection();

			if (connection != null) {
				String SQL = null;

				if (tripType.equalsIgnoreCase("one_way")) {
					SQL = "select f.numSeats seats_available, f.flight_id, f.flight_number,f.source,  " +
							"f.destination,f.numSeats,f.intermediate,  f.time_flight,f.intermediate_arrival_time, " +
							" f.intermediate_departure_time,f.airline,ticket_price from ars1.flight f left outer join ars1.journey t  " +
							"on f.flight_id = t.flight_id where (lower(source) in (?) and lower(intermediate) in (?) )  " +
							"or  ( lower(destination) in (?) and lower(intermediate) in (?)) " +
							"or  ( lower(source) in (?) and lower(destination) in (?))  " +
							"and (t.flight_id is null or journey_date not in (?))" +
							" union select (numSeats-sum(seats_booked)) seats_available, f.flight_id, f.flight_number, f.source, f.destination, f.numSeats, " +
							"f.intermediate, f.time_flight, f.intermediate_arrival_time,  f.intermediate_departure_time, f.airline,ticket_price from ars1.flight f, " +
							"ars1.journey t  where f.flight_id = t.flight_id  and journey_date = ? " +
							" and (lower(source) in (?) or lower(intermediate) in (?) )  and  " +
							"( lower(destination) in ( ?)or lower(intermediate) in (?))  " +
							"group by journey_date, f.flight_id  having sum(seats_booked)+? < (select numSeats from ars1.flight where flight_id = f.flight_id);";
					stmt = connection.prepareStatement(SQL);
					stmt.setString(1, source.toLowerCase().trim());
					stmt.setString(2, destination.toLowerCase().trim());
					stmt.setString(3, destination.toLowerCase().trim());
					stmt.setString(4, source.toLowerCase().trim());
					// System.out.println(formatter.format(startDate));
					
					stmt.setString(5, source.toLowerCase().trim());
					stmt.setString(6, destination.toLowerCase().trim());
					stmt.setString(7, startDate);
					stmt.setString(8, startDate);
					stmt.setString(9, source.toLowerCase().trim());
					stmt.setString(10, source.toLowerCase().trim());
					stmt.setString(11, destination.toLowerCase().trim());
					stmt.setString(12, destination.toLowerCase().trim());
					stmt.setInt(13, numTravelers);

					System.out.println(stmt.toString());

				} else {
					SQL = "select f.numSeats seats_available, f.flight_id, f.flight_number,f.source, "
							+ "f.destination,f.numSeats,f.intermediate, f.time_flight,f.intermediate_arrival_time,f.intermediate_departure_time,"
							+ "f.airline,ticket_price from ars1.flight f left outer join ars1.journey t on f.flight_id = t.flight_id"
							+ " where (lower(source) in (?, ?) and lower(intermediate) "
							+ "in (?,?) ) or  ( lower(destination) in (?,?) "
							+ "and lower(intermediate) in (?,?)) or  ( lower(source) in (?,?) "
							+ "and lower(destination) in (?,?))"
							+ " and (t.flight_id is null or journey_date not in (?,?)) "
							+ "union select (numSeats-sum(seats_booked)) seats_available, f.flight_id, f.flight_number, f.source, f.destination, "
							+ "f.numSeats, f.intermediate, f.time_flight, f.intermediate_arrival_time,  "
							+ "f.intermediate_departure_time, f.airline,ticket_price from ars1.flight f, ars1.journey t where f.flight_id = "
							+ "t.flight_id  and journey_date = ? and (lower(source) in (?) or lower(intermediate) in "
							+ "(?) ) and  ( lower(destination) in ( ?) or lower(intermediate) in (?)) "
							+ "group by journey_date, f.flight_id  having sum(seats_booked)+? < "
							+ "(select numSeats from ars1.flight where flight_id = f.flight_id) union "
							+ "select (numSeats-sum(seats_booked)) seats_available, f.flight_id, f.flight_number, f.source, "
							+ "f.destination, f.numSeats, f.intermediate, f.time_flight, f.intermediate_arrival_time,  "
							+ "f.intermediate_departure_time, f.airline,ticket_price from ars1.flight f, ars1.journey t "
							+ "where f.flight_id = t.flight_id  and journey_date = ? and (lower(source) in (?) "
							+ "or lower(intermediate) in (?) ) and  ( lower(destination) in ( ?) "
							+ "or lower(intermediate) in (?)) group by journey_date, f.flight_id  "
							+ "having sum(seats_booked) + ? < (select numSeats from ars1.flight where flight_id = f.flight_id);";
					stmt = connection.prepareStatement(SQL);
					stmt.setString(1, source.toLowerCase().trim());
					stmt.setString(2, destination.toLowerCase().trim());
					stmt.setString(3, source.toLowerCase().trim());
					stmt.setString(4, destination.toLowerCase().trim());
					stmt.setString(5, source.toLowerCase().trim());
					stmt.setString(6, destination.toLowerCase().trim());
					stmt.setString(7, source.toLowerCase().trim());
					stmt.setString(8, destination.toLowerCase().trim());
					stmt.setString(9, source.toLowerCase().trim());
					stmt.setString(10, destination.toLowerCase().trim());
					stmt.setString(11, source.toLowerCase().trim());
					stmt.setString(12, destination.toLowerCase().trim());
					stmt.setString(13, startDate);
					stmt.setString(14, returnDate);
					stmt.setString(15, startDate);
					stmt.setString(16, source.toLowerCase().trim());
					stmt.setString(17, source.toLowerCase().trim());
					stmt.setString(18, destination.toLowerCase().trim());
					stmt.setString(19, destination.toLowerCase().trim());
					stmt.setInt(20, numTravelers);

					stmt.setString(21, returnDate);
					stmt.setString(22, destination.toLowerCase().trim());
					stmt.setString(23, destination.toLowerCase().trim());
					stmt.setString(24, source.toLowerCase().trim());
					stmt.setString(25, source.toLowerCase().trim());
					stmt.setInt(26, numTravelers);

					System.out.println(stmt.toString());

				}

				stmt.setFetchSize(50);
				ResultSet resultSet = stmt.executeQuery();

				while (resultSet.next()) {

					Flight flight = new Flight();
					flight.setFlightId(resultSet.getLong("flight_id"));
					flight
							.setFlightNumber(resultSet
									.getString("flight_number"));
					flight.setSource(resultSet.getString("source"));
					flight.setDestination(resultSet.getString("destination"));
					flight.setIntermediate(resultSet.getString("intermediate"));
					flight.setIntermediateArrivalTime(resultSet
							.getString("intermediate_arrival_time"));
					flight.setIntermediateDepartureTime(resultSet
							.getString("intermediate_departure_time"));
					flight.setNumAvailableSeats(resultSet
							.getInt("seats_available"));
					flight.setTimeOfFlight(resultSet.getString("time_flight"));
					flight.setAirlineName(resultSet.getString("airline"));
					flight.setTicketPrice(resultSet.getDouble("ticket_price"));

					if (lstFlights.contains(flight)) {
						lstFlights.remove(flight);
					}

					lstFlights.add(flight);

				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {

			}

		}

		return lstFlights;
	}

	@Override
	public List<Flight> getFlightDetails(String flightIds) {

		System.out.println(flightIds);
		Statement stmt = null;
		Connection connection = null;
		List<Flight> lstFlights = new ArrayList<Flight>();
		try {
			connection = getConnection();
			if (connection != null) {
				String SQL = "select * from ars1.flight where flight_id  in ("
						+ flightIds + ");";
				stmt = connection.createStatement();
				stmt.setFetchSize(50);
				ResultSet resultSet = stmt.executeQuery(SQL);

				System.out.println(SQL);

				while (resultSet.next()) {
					Flight flight = new Flight();
					flight.setFlightId(resultSet.getLong("flight_id"));
					flight
							.setFlightNumber(resultSet
									.getString("flight_number"));
					flight.setSource(resultSet.getString("source"));
					flight.setDestination(resultSet.getString("destination"));
					flight.setIntermediate(resultSet.getString("intermediate"));
					flight.setStatus(resultSet.getString("flight_status"));
					flight.setNumSeats(resultSet.getInt("numSeats"));
					flight.setPilotName(resultSet.getString("pilot_name"));
					flight.setIntermediateArrivalTime(resultSet
							.getString("intermediate_arrival_time"));
					flight.setIntermediateDepartureTime(resultSet
							.getString("intermediate_departure_time"));
					flight.setTimeOfFlight(resultSet.getString("time_flight"));
					flight.setAirlineName(resultSet.getString("airline"));
					flight.setTicketPrice(resultSet.getDouble("ticket_price"));

					lstFlights.add(flight);
				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {

			}

		}
		return lstFlights;

	}

	public Flight changeFlightInfo(String flightNumber, String airlineName,
			String source, String destination, int numSeats,
			int numAvailableSeats, String pilotName, String intermediate,
			String intermediateArrivalTime, String intermediateDepartureTime,
			String timeOfFlight, double ticketPrice, String status,
			long flightId) throws DAOException {
		/*
		 * Statement stmt = null; Connection connection = null; Flight
		 * flight=new Flight(); try { connection = getConnection(); if
		 * (connection != null) { String
		 * SQL="update ars1.flight set flight_number='"
		 * +flightNumber+", 'airline='"
		 * +airlineName+"'source='"+source+"'destination='"
		 * +destination+"'numSeats='"
		 * +numAvailableSeats+"'pilot_name='"+pilotName
		 * +"'intermediate_arrival_time='"
		 * +intermediateArrivalTime+"'intermediate_departure_time='"
		 * +intermediateDepartureTime
		 * +"'time_flight='"+timeOfFlight+"'ticket_price='"
		 * +ticketPrice+"'flight_status='"
		 * +status+"' where  flight_id='"+flight_id+"'"; stmt =
		 * connection.createStatement(); ResultSet resultSet =
		 * stmt.executeQuery(SQL); }catch (SQLException e) { // TODO
		 * Auto-generated catch block e.printStackTrace(); } // Very important
		 * step. Never miss out on this! finally { try { stmt.close();
		 * connection.close(); } catch (Exception ex) {
		 * 
		 * }
		 * 
		 * }
		 */return null;
	}

	@Override
	public List<FlightUpdate> retrieveFlightUpdates(long personId) {

		PreparedStatement stmt = null;
		Connection connection = null;
		List<FlightUpdate> lstFlightUpdates = new ArrayList<FlightUpdate>();
		try {
			connection = getConnection();
			if (connection != null) {
				String SQL = "select distinct f.*,  fu.* from ars1.flight f , ars1.journey j, ars1.flight_updates fu "
						+ "where f.flight_id = j.flight_id and fu.flight_id = j.flight_id and fu.journey_date = j.journey_date and "
						+ "person_id = ? and isRead = 0;";

				stmt = connection.prepareStatement(SQL);
				stmt.setLong(1, personId);

				System.out.println(stmt);

				ResultSet resultSet = stmt.executeQuery();

				System.out.println(SQL);

				while (resultSet.next()) {
					FlightUpdate fu = new FlightUpdate();
					Flight flight = new Flight();
					flight.setFlightId(resultSet.getLong("flight_id"));
					flight
							.setFlightNumber(resultSet
									.getString("flight_number"));
					flight.setSource(resultSet.getString("source"));
					flight.setDestination(resultSet.getString("destination"));
					flight.setIntermediate(resultSet.getString("intermediate"));
					flight.setIntermediateArrivalTime(resultSet
							.getString("intermediate_arrival_time"));
					flight.setIntermediateDepartureTime(resultSet
							.getString("intermediate_departure_time"));

					flight.setTimeOfFlight(resultSet.getString("time_flight"));
					flight.setAirlineName(resultSet.getString("airline"));
					Journey j = new Journey();
					j.setDateOfJourney(resultSet.getString("journey_date"));
					fu.setFlight(flight);
					fu.setJourney(j);
					fu.setMessage(resultSet.getString("message"));

					lstFlightUpdates.add(fu);
				}

				// UPdate the isread flag to 1 after reading the message
				SQL = "update ars1.flight f , "
						+ "ars1.journey j, ars1.flight_updates fu set isRead =1 "
						+ "where f.flight_id = j.flight_id and fu.flight_id = j.flight_id and fu.journey_date = j.journey_date and person_id = ? and isRead = 0;";

				stmt = connection.prepareStatement(SQL);
				stmt.setLong(1, personId);

				stmt.executeUpdate();

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {

			}

		}
		/*
		 * select distinct f.*, fu.* from ars1.flight f , ars1.journey j,
		 * ars1.flight_updates fu where f.flight_id = j.flight_id and
		 * fu.flight_id = j.flight_id and person_id = 1 and isRead = 0;
		 */

		return lstFlightUpdates;
	}

	@Override
	public List<String> retrieveCities() throws DAOException {
		Statement stmt = null;
		Connection connection = null;
		List<String> lstCities = new ArrayList<String>();
		try {
			connection = getConnection();
			if (connection != null) {
				String SQL = "select * from (select distinct upper(source) city from ars1.flight union select distinct upper(intermediate) city from ars1.flight union select distinct upper(destination) city from ars1.flight) cities order by city asc;";
				stmt = connection.createStatement();
				stmt.setFetchSize(50);
				ResultSet resultSet = stmt.executeQuery(SQL);
				while (resultSet.next()) {

					lstCities.add(resultSet.getString("city"));
				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {

			}

		}
		return lstCities;
	}

}